// 引入 express
const express = require('express')
const app = express()

// 引入 body-parser
const bodyParser = require('body-parser')
app.use(bodyParser.urlencoded({
    extended: false
}))

// 引入 mysql
const mysql = require('mysql')
const dbconfig = require('./database')
const pool = mysql.createPool(dbconfig.mysql)

// 引入 ejs
const ejs = require('ejs')
app.set('view engine', 'ejs') // 设置要使用的模板引擎

// 静态文件目录
const staticDir = 'page'
app.use('/', express.static(staticDir))
app.set('views', staticDir) // 设置模板文件所在目录

// 主页
app.get('/discover.html', function (req, res) {
    var sql = "select * from work order by workID"
    pool.query(sql, function (err, works) {
        if (err) throw err
        res.render('discover', {
            works: works
        })
    })
})

//找项目
app.get('/findProject2.html',function(req, res){
    var sql = "select * from orderupload order by orderID"
    pool.query(sql, function (err, orders) {
        if (err) throw err
        res.render('findProject2', {
            orders: orders
        })
    })
})

//找人
app.get('/findProject1.html',function(req, res){
    var sql = "select * from user order by userID"
    pool.query(sql, function (err, user) {
        if (err) throw err
        res.render('findProject1', {
            user: user
        })
    })
})

// 用户登陆
app.post('/login', function (req, res) {
    console.log(req.body.username)
    console.log(req.body.userpwd)
    var sql = "select * from user where userName = ? and userPassword = ?"
    var sqlParams = [req.body.username, req.body.userpwd]
    pool.query(sql, sqlParams, function (err, result) {
        if (err) throw err
        if (result.length == 1) {
            var info = {
                status: true
            }
            // res.end(JSON.stringify(info))
            res.redirect(`/main.html`)
        } else {
            var info = {
                status: false
            }
            
        }
    })
})

//注册
app.post('/logon',function(req,res){
    var usernaem = req.body.username
    var userpwd = req.body.userpwd
    var email = req.body.email
    var phone = req.body.phone
    sqlParams = {
        userName:usernaem,
        userPassword:userpwd,
        userEmail:email,
        userPhone:phone,
    }
    sql = 'insert into user set ?'
        pool.query(sql, sqlParams, function (err, result) {
            if (err) throw err
            // console.log(result)
            res.redirect(`/choose.html`)
        })
})

//输入订单
app.post('/publish1',function(req,res){
    var ordername = req.body.ordername
    var orderinfo = req.body.orderinfo
    var ordertype = req.body.lx
    var orderlong = req.body.orderlong
    var orderadress = req.body.orderadress
    var ordercost = req.body.ordercost
    var ordertime = req.body.ordertime
    var orderemail =req.body.orderemail
    sqlParams = {
        userID:1,
        orderName:ordername,
        orderInfo:orderinfo,
        ordertype:ordertype,
        orderLong:orderlong,
        orderAdress:orderadress,
        orderCost:ordercost,
        orderTime:ordertime,
        orderEmail:orderemail,
    }
    sql = 'insert into orderupload set ?'
        pool.query(sql, sqlParams, function (err, result) {
            if (err) throw err
            // console.log(result)
            res.redirect(`/publish2.html`)
        })
})

//显示订单信息
app.get('/publish3.html',function(req, res){
    var sql = "select * from orderupload order by orderID desc"
    pool.query(sql, function (err, orders) {
        if (err) throw err
        res.render('publish3', {
            orders: orders
        })
    })
})

//主界面显示订单信息
app.get('/main.html',function(req, res){
    var sql = "select * from orderupload where userID='1' order by orderID desc"
    pool.query(sql, function (err, orders) {
        if (err) throw err
        res.render('main', {
            orders: orders
        })
    })
})

//详情页面
app.get('/myProject.html', function (req, res) {
    var orderid = req.query.order
    var sql = 'select * from orderupload,user where orderupload.userID=user.userID and orderID = ? '
    var sqlParams = [orderid]
    pool.query(sql, sqlParams, function (err, orders) {
        if (err) throw err
        res.render('myProject', {
            orders: orders
        })
    })
})

//详情页面
app.get('/project.html', function (req, res) {
    var orderid = req.query.order
    var sql = 'select * from orderupload,user where orderupload.userID=user.userID and orderID = ? '
    var sqlParams = [orderid]
    pool.query(sql, sqlParams, function (err, orders) {
        if (err) throw err
        res.render('project', {
            orders: orders
        })
    })
})

//连接职位与用户
app.post('/relate',function(req,res){
    var orderid = req.body.order
    var positionid =req.body.position
    var userid = req.body.user
    var sql = 'select count(*) as num from relation where orderID=? and userID=? and positionID=? '
    var sqlParams=[orderid,userid,positionid]
        pool.query(sql, sqlParams, function (err, result) {
            if (err) throw err
            if(result[0].num == 0){
                sql = 'insert into relation set ?'
                sqlParams = {
                    orderID:orderid,
                    positionID:positionid,
                    userID:userid,
                    relationType:'通过'
                }
                pool.query(sql, sqlParams, function (err, result) {
                    if (err) throw err
                    // console.log(result)
                    // res.redirect(`/main2.html`)
                    var info = {
                        status: true
                    }
                    res.end(JSON.stringify(info))
                })
            }
            if(result[0].num == 1)
            {
                var info = {
                    status: false
                }
                res.end(JSON.stringify(info))
            }
        })
})

//主界面显示应召信息
app.get('/main2.html',function(req, res){
    var sql = "select * from orderupload,relation,user,orderposition where orderupload.orderID=relation.orderID and relation.userID=user.userID and relation.positionID=orderposition.positionID and relation.userID=1 "
    pool.query(sql, function (err, position) {
        if (err) throw err
        res.render('main2', {
            position: position
        })
    })
})

//删除
app.post('/delete',function(req,res){
    var orderid = req.body.order
    var sql = 'DELETE FROM orderupload where orderID = ?'
    var sqlParams = [orderid]
    pool.query(sql, sqlParams, function (err, result) {
        if (err) throw err
        // console.log(result)
    })
    // res.redirect(`/main.html`)
})

var server = app.listen(8888, 'localhost', function () {
    var host = server.address().address
    var port = server.address().port
    console.log(`Server running at http://${host}:${port}/choose.html\n`)
})